package com.ceba.test.modules.admin.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.ceba.base.helper.LoggerHelper;
import com.ceba.cebautils.StringUtil;
import org.json.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.ceba.test.base.enums.StampTypeEnum;
import com.ceba.base.web.dao.IBaseDao;
import com.ceba.test.modules.admin.entity.GStamp;
import com.ceba.test.modules.email.entity.MsgMail;

import abc.ID;
import abc.Result;
import abc.data.DataRow;
import abc.data.DataTable;
import abc.data.Sql;
import abc.data.SqlSessionManager;
import abc.data.dataSource.SqlDataSource;
import abc.data.store.IStore;
import abc.data.store.StoreManager;
import abc.object.IObject;
import abc.object.XObject;
import abc.util.StringUtils;

public class StampDao extends IBaseDao{

	static Logger logger = LoggerFactory.getLogger(StampDao.class);
	
	/**
	 * 获取某一个ID的数据对象
	 * @return
	 */	
	public  IObject getObject(String ID) {
		IStore store = StoreManager.create("g_stamp");
		IObject object = store.get(ID);
		return object;
	}
	
	public  DataTable getDataTable(String ID) throws Exception{
		Sql sql=new Sql("SELECT g_stamp.FILE_PATH,g_stamp.MATCH_NAME,g_stamp.MATCH_NAME AS FILE_NAME,"
				+ " g_stamp.IMG_WIDTH,g_stamp.IMG_HEIGHT, "
				+ " g_stamp.POSITION,g_stamp.COR_FLAG,"
				+ " g_stamp.STAMP_X,g_stamp.STAMP_Y,g_stamp.STAMP_PAGE,g_stamp.COR_STAMP_NAME "
				+",g_stamp.CER_NO,g_stamp.KEY_NAME,g_stamp.ENCODE_SIGNTYPE ,g_stamp.SEAL_PAGE_MODE,g_stamp.SEAL_TYPE,g_stamp.STORAGE_ADDRESS_ID"
				+ " FROM g_stamp  "
				+ " WHERE g_stamp.ID=?");
		sql.addParameter(ID);
		return SqlSessionManager.getDataTable(sql);

	}
	
	
	/**
	 * 获取电子签名地址信息
	 * @return
	 */	
	public static IObject getSignObject() {
		IStore store = StoreManager.create("g_stamp");
		IObject object = store.get("sign");
		return object;
	}
	
	
	public static DataTable getSignDataTable() {
		Sql sql=new Sql("SELECT g_stamp.FILE_PATH,g_stamp.MATCH_NAME,g_stamp.MATCH_NAME AS FILE_NAME,g_stamp.IMG_WIDTH,g_stamp.IMG_HEIGHT ,g_stamp.CER_NO,g_stamp.ENCODE_SIGNTYPE,g_stamp.getDataTable "
				+ " FROM g_stamp  "
				+ " WHERE g_stamp.ID='sign'");
		return SqlSessionManager.getDataTable(sql);

	}
	
	//created by dingf
	public GStamp getFirstStampIdByUserId(String userId) {
		GStamp gs = new GStamp();
		Sql sql=new Sql("SELECT ID FROM g_stamp  WHERE USER_ID =? limit 1");
		sql.addParameter(userId);
		DataTable dt =  SqlSessionManager.getDataTable(sql);
		if(dt !=null && dt.getRowCount() >0){
			parseDrToStamp(dt.getRow(0), gs);
			return gs;
		}
		return null;
	}
	
	/**
	 * 获取电子签名地址
	 * @return
	 */	
	public static IObject getStampObject() {
		IStore store = StoreManager.create("g_stamp");
		IObject object = store.get("stamp");
		return object;
	}
	
	public  DataTable getStampDataTable() {
		Sql sql=new Sql("SELECT g_stamp.FILE_PATH,g_stamp.MATCH_NAME,g_stamp.MATCH_NAME AS FILE_NAME,"
				+ " g_stamp.IMG_WIDTH,g_stamp.IMG_HEIGHT, g_stamp.COR_STAMP_NAME,g_stamp.USER_ID,g_stamp.g_stamp,"
				 +" POSITION,COR_FLAG,"
				 +" STAMP_X,STAMP_Y,STAMP_PAGE "
				 + " ,g_stamp.CER_NO,g_stamp.ENCODE_SIGNTYPE.g_stamp.STORAGE_ADDRESS_ID "
				+ " FROM g_stamp  "
				+ " WHERE g_stamp.ID='stamp'");
		return SqlSessionManager.getDataTable(sql);
	}
	
	public  GStamp getStampObjectDataTable() {
		GStamp gs = new GStamp();
		DataTable dt = getStampDataTable();
		if(dt !=null && dt.getRowCount()>0){
			DataRow dr = dt.getRow(0);
			parseDrToStamp(dr,gs);
			return gs;
		}else{
			return null;
		}

	}
	
	public  DataTable getUserStampDataTable(String userId) {
		Sql sql1=new Sql("SELECT g_file.FILE_PATH,g_stamp.MATCH_NAME,g_stamp.ID,g_file.FILE_NAME,g_stamp.COR_STAMP_NAME,g_stamp.USER_ID,"
				+ " g_stamp.IMG_WIDTH,g_stamp.IMG_HEIGHT, "
				 +" g_stamp.POSITION, g_stamp.COR_FLAG,"
				 +" g_stamp.STAMP_X,g_stamp.STAMP_Y,g_stamp.STAMP_PAGE "
				 + " ,g_stamp.CER_NO,g_stamp.ENCODE_SIGNTYPE,g_file.STORAGE_ADDRESS_ID  fileStorageId ,g_stamp.STORAGE_ADDRESS_ID gsStorageId "
				+ " FROM g_file  "
				+ " LEFT OUTER JOIN g_stamp ON g_file.ID=g_stamp.FILE_ID"
				+ " WHERE g_stamp.USER_ID=?");
		sql1.addParameter(userId);
		DataTable dt=SqlSessionManager.getDataTable(sql1);
		if(dt==null || dt.getRowCount()<=0){
			sql1=new Sql("SELECT g_stamp.FILE_PATH,g_stamp.MATCH_NAME,g_stamp.ID,g_stamp.MATCH_NAME AS FILE_NAME,g_stamp.COR_STAMP_NAME,g_stamp.USER_ID,"
					+ " g_stamp.IMG_WIDTH,g_stamp.IMG_HEIGHT, "
					 +" g_stamp.POSITION, g_stamp.COR_FLAG,"
					 +" g_stamp.STAMP_X,g_stamp.STAMP_Y,g_stamp.STAMP_PAGE "
					 + " ,g_stamp.CER_NO,g_stamp.ENCODE_SIGNTYPE,g_stamp.STORAGE_ADDRESS_ID "
					+ " FROM g_stamp  "
					+ " WHERE g_stamp.USER_ID=?");
			sql1.addParameter(userId);
			dt=SqlSessionManager.getDataTable(sql1);
		}
		return dt;
	}
	
	public GStamp getOneUserStamp(String userId){
		DataTable dataTable=getUserStampDataTable(userId);
		if(dataTable != null && dataTable.size() > 0){
			DataRow dataRow=dataTable.getRow(0);
			GStamp gStamp=new GStamp();
			parseDrToStamp(dataRow, gStamp);
			return gStamp;
		}
		return null;
	}
	
	private void setPublicObject(GStamp g,XObject object){
		object.put("COR_STAMP_NAME",g.getCorStampName());
		object.put("MATCH_NAME",g.getMatchName());
		object.put("IMG_WIDTH", g.getImgWidth());
		object.put("IMG_HEIGHT", g.getImgHeight());
		object.put("STAMP_PAGE", g.getStampPage());
		object.put("FILE_PATH", g.getFilePath());
		object.put("COR_FLAG", g.getCorFlag());
		object.put("STAMP_X", g.getStamp_x());
		object.put("STAMP_Y", g.getStamp_y());
		object.put("CER_NO", g.getCerNo());
		object.put("KEY_NAME", g.getKeyName());
		object.put("SEAL_TYPE", g.getSealType());
		object.put("ENCODE_SIGNTYPE", g.getEncodeSignType());
		object.put("SEAL_PAGE_MODE", g.getSealPageMode());

		if (!StringUtils.isNullOrEmpty(g.getStorageAddressId())) {
			object.put("STORAGE_ADDRESS_ID", g.getStorageAddressId());
		}
		object.setUpdateUser();
		object.setUpdateTime();
	}
	/**
	 * 增加章配置
	 * @param g
	 * @return
	 */
	public boolean  addStamp(GStamp g){
		//id为空是新建记录
		IStore store = StoreManager.create("g_stamp");
		XObject object = new XObject("ID",g.getId());
		setPublicObject(g,object);
		object.put("USER_ID",g.getUserId());
		object.put("USER_ID",g.getUserId());
		object.put("POSITION", g.getPosition());
		return addStamp( store,object);
	}
	
	/**
	 * 更新章配置
	 * @param g
	 * @return
	 */
	public boolean  updateStamp(GStamp g){
		IStore store = StoreManager.create("g_stamp");
		XObject object = new XObject("ID",g.getId());
		setPublicObject(g,object);
		if(StringUtils.isNotNullAndEmpty( g.getFilePath())){
			object.put("FILE_PATH",  g.getFilePath());
		}
		return updateStamp(store,object);
	}
	
	/**
	 * 增加章配置
	 * @return
	 */
	public boolean  addStamp(IStore store,XObject object){
		if(store.insert(object)>0){
			return true;
		}
		return false;
	}
	
	/**
	 * 更新章配置
	 * @return
	 */
	public boolean  updateStamp(IStore store,XObject object){
		
		if(store.update(object)>0){
			return true;
		}
		return false;
	}
	
	/**
	 * 根据id获取报告章
	 * @param stampID
	 * @return
	 */
	public  DataTable getCorStampByStampId(String stampID) throws Exception{
		DataTable dt = null;
		if(!StringUtils.isNullOrEmpty(stampID)){
			Sql sql = new Sql("SELECT u_user.Name,"
					 +" g_stamp.* "
					 +" FROM g_stamp"
					 +" LEFT OUTER JOIN u_user ON g_stamp.USER_ID=u_user.ID"
					 +" WHERE g_stamp.ID='"+stampID+"'");
			dt = SqlSessionManager.getDataTable(sql);
		}
		return dt;
	}
	
	
	
	/**
	 * 获取可选的报告章
	 * @return
	 */
	public  List<GStamp> getSelectCorStamps(String userId) throws Exception{
		DataTable dt = null;
		Sql sql = null;
		if(StringUtil.isEmptyString(userId)){
			sql = new Sql("SELECT gs.ID as ID,gs.MATCH_NAME,gs.COR_STAMP_NAME,gs.IMG_WIDTH,"
					 + " gs.IMG_HEIGHT,gs.FILE_PATH"
					 + " ,gs.CER_NO,gs.ENCODE_SIGNTYPE,gs.STORAGE_ADDRESS_ID "
					 +" FROM g_stamp as gs "
					 +" WHERE gs.COR_FLAG='"+StampTypeEnum.ENTERPRISE_COR_STAMP_FLAG.getType()+"' ");
		}else{
			sql = new Sql("SELECT gu.USER_AUTHORIZATION_ID as ID,gs.MATCH_NAME,gs.CER_NO,gs.ENCODE_SIGNTYPE,gs.COR_STAMP_NAME,gs.IMG_WIDTH,"
				 + " gs.IMG_HEIGHT,gs.FILE_PATH,gs.STORAGE_ADDRESS_ID"
				 +" FROM g_user_cor_stamp as gu "
				 +" LEFT OUTER JOIN g_stamp as gs ON gu.USER_AUTHORIZATION_ID = gs.ID "
				 +" LEFT OUTER JOIN u_user ON gu.USER_ID=u_user.ID"
				 +" WHERE gs.COR_FLAG='"+StampTypeEnum.ENTERPRISE_COR_STAMP_FLAG.getType()+"' AND gu.USER_ID = '" +userId+ "'");
		}
		
		dt = SqlSessionManager.getDataTable(sql);
		List<GStamp> list = new ArrayList<GStamp>();
		if(dt !=null && dt.getRowCount()>0){
			for(int i = 0 ; i < dt.getRowCount() ; i ++){
				DataRow dr = dt.getRow(i);
				GStamp gs = new GStamp();
				parseDrToStamp(dr, gs);
				list.add(gs);
			}
		}
		
		return list;
	}
	
	/**
	 * 获取可选的报告章
	 * @return
	 */
	public  String getSelectCorStampsStrings(String userId) throws Exception{
		DataTable dt = null;
		Sql sql = new Sql("SELECT gu.USER_AUTHORIZATION_ID as ID,gs.MATCH_NAME,gs.COR_STAMP_NAME AS NAME,gs.STORAGE_ADDRESS_ID"
				 +" FROM g_user_cor_stamp as gu "
				 +" LEFT OUTER JOIN g_stamp as gs ON gu.USER_AUTHORIZATION_ID = gs.ID "
				 +" LEFT OUTER JOIN u_user ON gu.USER_ID=u_user.ID"
				 +" WHERE gs.COR_FLAG='"+StampTypeEnum.ENTERPRISE_COR_STAMP_FLAG.getType()+"' AND gu.USER_ID = '" +userId+ "'");
		dt = SqlSessionManager.getDataTable(sql);
		if(dt !=null && dt.getRowCount()>0){
			return dt.toJSONArrayString();
		}
		return "";
	}
	/**
	 * 获取用户个人章
	 * @param stampID
	 * @return
	 * @throws Exception
	 */
	public List<GStamp> getUserGStampById(String stampID) throws Exception{
		Sql sql = new Sql("SELECT u_user.`Name`,g_stamp.USER_ID,g_stamp.MATCH_NAME,g_stamp.IMG_WIDTH,"
				 + " g_stamp.IMG_HEIGHT,FILE_PATH,"
				 +" g_stamp.POSITION, g_stamp.COR_FLAG,g_stamp.COR_STAMP_NAME,g_stamp.USER_ID,"
				 +" g_stamp.STAMP_X,g_stamp.STAMP_Y,g_stamp.STAMP_PAGE"
				 + " ,g_stamp.CER_NO,g_stamp.ENCODE_SIGNTYPE,g_stamp.SEAL_TYPE,g_stamp.STORAGE_ADDRESS_ID"
				 +" FROM g_stamp"
				 +" LEFT OUTER JOIN u_user ON g_stamp.USER_ID=u_user.ID"
				 +" WHERE g_stamp.ID=?");
		sql.addParameter(stampID);
		DataTable dt = SqlSessionManager.getDataTable(sql);
		List<GStamp> list = new ArrayList<GStamp>();
		if(dt !=null && dt.getRowCount()>0){
			for(int i = 0 ; i < dt.getRowCount() ; i ++){
				DataRow dr = dt.getRow(i);
				GStamp gs = new GStamp();
				parseDrToStamp(dr, gs);
				list.add(gs);
			}
		}
		return list;
	}
	
	/*
	 * 根据用户名获取其可选的公章
	 */
	public List<GStamp> getCorStampsByUserId(String userId,String corType,String authorization) throws Exception{
		if ("9".equalsIgnoreCase(corType)) {
			corType = "' and (gs.COR_FLAG = '9' or gs.COR_FLAG = '11') ";
		} else {
			corType = "' and gs.COR_FLAG = '" + corType + "' ";
		}

		Sql sql = new Sql("SELECT gs.ID,gs.COR_STAMP_NAME, gs.STORAGE_ADDRESS_ID,gs.CER_NO,gs.ENCODE_SIGNTYPE FROM g_stamp as gs"
				+ "	LEFT OUTER JOIN g_user_cor_stamp as gu ON gu.USER_AUTHORIZATION_ID = gs.ID "
				+ " WHERE gu.USER_ID = '" + userId + "'and gu.AUTHORIZATION_TYPE='" + authorization + corType
				+ " GROUP BY gs.COR_STAMP_NAME,gs.ID,gs.STORAGE_ADDRESS_ID,gs.CER_NO,gs.ENCODE_SIGNTYPE");
		DataTable dt = SqlSessionManager.getDataTable(sql);
		List<GStamp> list = new ArrayList<GStamp>();
		if(dt !=null && dt.getRowCount()>0){
			for(int i = 0 ; i < dt.getRowCount() ; i ++){
				DataRow dr = dt.getRow(i);
				GStamp gs = new GStamp();
				parseDrToStamp(dr, gs);
				list.add(gs);
			}
		}
		
		return list;
	}
	
	
	
	/*
	 * 根据可选的公章
	 */
	public List<GStamp> getCorStamp(String corType) throws Exception{
		if(corType.equals("9")){
			corType = "WHERE COR_FLAG = '9' OR COR_FLAG = '11'";
		} else {
			corType = "WHERE gs.COR_FLAG = '" + corType + "'";
		}

		Sql sql = new Sql("SELECT gs.ID,gs.COR_STAMP_NAME ,gs.CER_NO,gs.ENCODE_SIGNTYPE,gs.STORAGE_ADDRESS_ID FROM g_stamp as gs " + corType);
		DataTable dt = SqlSessionManager.getDataTable(sql);
		List<GStamp> list = new ArrayList<GStamp>();
		if(dt !=null && dt.getRowCount()>0){
			for(int i = 0 ; i < dt.getRowCount() ; i ++){
				DataRow dr = dt.getRow(i);
				GStamp gs = new GStamp();
				parseDrToStamp(dr, gs);
				list.add(gs);
			}
		}
		
		return list;
		
	}
	
	
	/**
	 * 关联用户公章
	 * @param userId
	 * @param gStampId
	 * @return
	 */
	public boolean addUserCorStamp(String userId,String gStampId,int type){
		IStore store = StoreManager.create("g_user_cor_stamp");
		XObject object = new XObject("ID",ID.getGuid());
		object.put("USER_AUTHORIZATION_ID", gStampId);
		object.put("USER_ID",userId);
		object.put("AUTHORIZATION_TYPE",type);
		object.setCreateTime();
		
		return addUserCorStamp(store, object);
	}		
	/**
	 * 关联用户公章
	 * @return
	 */
	public boolean addUserCorStamp(IStore store,XObject object){
		
		if(store.insert(object)>0){
			return true;
		}
		return false;
	}		
	

	
	/**
	 * 用户公章和用户是否在g_user_cor_stamp表中已经关联
	 * created by dingf
	 * @param userId
	 * @param gStampId
	 * @return
	 */
	public DataTable isUserCorStampConnected(String userId,String gStampId){
		SqlDataSource dataSource = new SqlDataSource();
		Sql sql = new Sql("select * from g_user_cor_stamp where USER_ID =? and USER_AUTHORIZATION_ID =? ");
		sql.addParameter(userId);
		sql.addParameter(gStampId);
		dataSource.getSelectCommands().add(sql);
		DataTable dt = dataSource.query(0);
		 if(dt!=null && dt.getRowCount()>0){
			return dt;
		 }
		 return null;
	}
	
	/**
	 * 用户公章和用户是否在g_user_cor_stamp表中已经关联
	 * created by dingf
	 * @param userId
	 * @return
	 */
	public DataTable canCorStampDelete(String userId){
		SqlDataSource dataSource = new SqlDataSource();
		Sql sql = new Sql("select * from g_stamp where USER_ID =? ");
		sql.addParameter(userId);
		dataSource.getSelectCommands().add(sql);
		DataTable dt = dataSource.query(0);
		return dt;
	}
	
	
	public boolean delUserCorStamp(String userId){
		Sql sql = new Sql("DELETE FROM g_user_cor_stamp WHERE USER_ID = '"+userId+"'");
		return SqlSessionManager.execute(sql);
	}
	
	public boolean deleteCorStampV1(String gStampId){
		Sql sql=new Sql("delete from g_user_cor_stamp where USER_AUTHORIZATION_ID='"+gStampId+"' ");
		return SqlSessionManager.execute(sql);
	}
	
	public boolean delUserCorStampByUserId(String userId){
		Sql sql = new Sql("DELETE FROM g_user_cor_stamp WHERE USER_ID = '"+userId+"'");
		return SqlSessionManager.execute(sql);
	}
	

	//从根据USER_ID从g_stamp表中取出所有记录的ID,COR_STAMP_NAME
	public List<GStamp>  getUserStampDTByUserId(String userId) throws Exception {
		Sql sql=new Sql("select ID,COR_STAMP_NAME from g_stamp where USER_ID =?");
		sql.addParameter(userId);
		DataTable dt= SqlSessionManager.getDataTable(sql);
		List<GStamp> list = new ArrayList<GStamp>();
		if(dt !=null && dt.getRowCount()>0){
			for(int i = 0 ; i < dt.getRowCount() ; i ++){
				DataRow dr = dt.getRow(i);
				GStamp gs = new GStamp();
				parseDrToStamp(dr, gs);
				list.add(gs);
			}
		}
		
		return list;
	}
	
	
	/**
	 * 获取可选的用户章
	 * created by dingf
	 * @return
	 */
	public static String getSelectedUserStampsStrings(String userId) throws Exception{
		DataTable dt = null;
		Sql sql = new Sql("SELECT gs.ID as ID,gs.COR_STAMP_NAME AS NAME"
				 +" FROM g_stamp as gs "
				 +" WHERE gs.USER_ID = '" +userId+ "'");
		dt = SqlSessionManager.getDataTable(sql);
		if(dt !=null && dt.getRowCount()>0){
			return dt.toJSONArrayString();
		}
		return "";
	}
	
	/*
	 * 根据章名称获取章
	 */
	public GStamp getStampByStampName(String stampName,int type){
		GStamp gs = new GStamp();
		Sql sql = new Sql("SELECT g_stamp.ID,g_stamp.FILE_PATH,g_stamp.MATCH_NAME,g_stamp.MATCH_NAME AS FILE_NAME,"
				+ " g_stamp.IMG_WIDTH,g_stamp.IMG_HEIGHT, "
				+ " g_stamp.POSITION,g_stamp.COR_FLAG,"
				+ " g_stamp.STAMP_X,g_stamp.STAMP_Y,g_stamp.STAMP_PAGE,g_stamp.COR_STAMP_NAME "
				 + " ,g_stamp.CER_NO,g_stamp.ENCODE_SIGNTYPE,g_stamp.STORAGE_ADDRESS_ID "
				+ " FROM g_stamp  "
				+ " WHERE g_stamp.COR_STAMP_NAME= '"+stampName+"' and g_stamp.COR_FLAG = '"+type+"'");
		DataTable dt = SqlSessionManager.getDataTable(sql);
		if(dt !=null && dt.getRowCount()>0){
			DataRow dr = dt.getRow(0);
			parseDrToStamp(dr,gs);
			return gs;
		}else{
			return null;
		}
	}
	/**
	 * 根据用户章名称判断章是否存在
	 * @param userStampName
	 * @return
	 */
	public boolean checkUserStampNameExist(String userStampName,String colFlag,String id)  throws Exception{
		Sql sql = new Sql("SELECT gs.ID as ID,gs.COR_STAMP_NAME"
				 + " ,gs.CER_NO,gs.ENCODE_SIGNTYPE ,gs.STORAGE_ADDRESS_ID"
				 +" FROM g_stamp as gs "
				 +" WHERE gs.COR_STAMP_NAME = '" +userStampName+ "' and COR_FLAG = "+colFlag +" and ID <>'"+id+"'");
		DataTable dt = SqlSessionManager.getDataTable(sql);
		if(dt !=null && dt.getRowCount() > 0){
			return true;
		}
		return false;
	}
	/**
	 * 判断是否有多个草稿水印
	 * @param 
	 * @return
	 */
	public boolean checkWarterExist()  throws Exception{
		Sql sql = new Sql("SELECT gs.ID as ID,gs.COR_STAMP_NAME,gs.STORAGE_ADDRESS_ID"
				 + " ,gs.CER_NO,gs.ENCODE_SIGNTYPE "
				+" FROM g_stamp as gs "
				+" WHERE COR_FLAG = '5'");
		DataTable dt = SqlSessionManager.getDataTable(sql);
		if(dt !=null && dt.getRowCount() > 0){
			return true;
		}
		return false;
	}
	
	/**
	 * 获取可选的报告章
	 * created by dingf
	 * @return
	 */
	public static String getSelectCorStampsStringsVersion1(String userId) throws Exception{
		DataTable dt = null;
		Sql sql = new Sql("SELECT gu.USER_AUTHORIZATION_ID as ID,gs.MATCH_NAME,gs.COR_STAMP_NAME AS NAME"
				 +" ,gs.STORAGE_ADDRESS_ID FROM g_user_cor_stamp as gu "
				 +" LEFT OUTER JOIN g_stamp as gs ON gu.USER_AUTHORIZATION_ID = gs.ID "
				 +" LEFT OUTER JOIN u_user ON gu.USER_ID=u_user.ID"
				 +" WHERE gs.COR_FLAG='"+StampTypeEnum.ENTERPRISE_COR_STAMP_FLAG.getType()+"' AND gu.USER_ID = '" +userId+ "' group by ID");
		dt = SqlSessionManager.getDataTable(sql);
		if(dt !=null && dt.getRowCount()>0){
			return dt.toJSONArrayString();
		}
		return "";
	}

	/**
	 * 
	 * 根据用户id获取授权章
	 * 
	 */
	
	public JSONObject queryAuthorizeStamp(String userId,String corFlag,Map<String,Object> dic) {
		if ("9".equalsIgnoreCase(corFlag)) {
			corFlag = "(g_stamp.COR_FLAG = '9' or g_stamp.COR_FLAG = '11')";
		} else {
			corFlag = "g_stamp.COR_FLAG = '"+corFlag+"'";
		}
		Sql sql = new Sql(
				"select g_stamp.ID,COR_STAMP_NAME AS CREDENTAIL_STAMP_NAME from g_stamp where g_stamp.ID in (select g_user_cor_stamp.USER_AUTHORIZATION_ID  from "+
				" g_user_cor_stamp where g_user_cor_stamp.USER_ID='"+userId+"') and " + corFlag);
		DataTable dt = SqlSessionManager.getDataTable(sql);
		if(dt !=null && dt.getRowCount() >0){
			return dt.toJSONObject();
		}else{
			return new JSONObject();
		}
		
		
		
	}
	
	
	
	
	/**
	 * 获取公司签章列表
	 * @return
	 */
	public JSONObject getCorStampList(String orderBy,String corFlag,Map<String,Object> dic) {
		Sql sql = null;
		SqlDataSource dataSource = new SqlDataSource();
		String key = (String) dic.get("key");
		if(StringUtil.isEmptyString(orderBy)){
			 orderBy = "g_stamp.UPDATE_TIME DESC";
		}
		StringBuffer ss = new StringBuffer("SELECT g_stamp.ID,COR_STAMP_NAME AS CREDENTAIL_STAMP_NAME"
				+ " ,MATCH_NAME,IMG_WIDTH,"
				+ " STAMP_X,g_stamp.STAMP_Y,g_stamp.STAMP_PAGE,g_stamp.COR_FLAG,g_stamp.SEAL_TYPE,g_stamp.STORAGE_ADDRESS_ID,"
				+ " IMG_HEIGHT,FILE_PATH FROM g_stamp ");
		//如果corFlg为all 则查询corFlag == 4 8 9 
		if(corFlag.equals("all")){
			ss.append("WHERE COR_FLAG = '4' OR COR_FLAG = '8' OR COR_FLAG = '9'");
		}else if(corFlag.equals("9")){
			ss.append("WHERE (COR_FLAG = '9' OR COR_FLAG = '11')");
		}else{
			ss.append("WHERE COR_FLAG = '"+corFlag+"'");
		}
		if(StringUtil.isEmptyString(key)){
			ss.append("ORDER BY " + orderBy);
			sql = new Sql(ss.toString());
		}else{
			//查询报告章时，可以根据报告章的名字匹配筛选
			ss.append(" AND (COR_STAMP_NAME like '%"+key+"%' OR MATCH_NAME like '%"+key+"%' ) "
					+ " ORDER BY " + orderBy);
			sql = new Sql(ss.toString());
		}
        dataSource.getSelectCommands().add(sql);
        DataTable dt = dataSource.query(0, dic);
		if(dt !=null && dt.getRowCount() >0){
            JSONObject jsonObject = dt.toJSONObject();
            jsonObject.put("total",getCorStampListCount(corFlag,key));
            return jsonObject;
		}else{
			return new JSONObject();
		}
	}

	/**
	 * 获取公司签章列表
	 * @return
	 */
	public JSONObject getWaterStapList(String orderBy,String corFlag,Map<String,Object> dic) {
		Sql sql = null;
		SqlDataSource dataSource = new SqlDataSource();
		String key = (String) dic.get("key");
		if(StringUtil.isEmptyString(orderBy)){
			 orderBy = "g_stamp.UPDATE_TIME DESC";
		}
		StringBuffer ss = new StringBuffer("SELECT g_stamp.ID,COR_STAMP_NAME AS CREDENTAIL_STAMP_NAME"
				+ " ,MATCH_NAME,IMG_WIDTH,"
				+ " STAMP_X,g_stamp.STAMP_Y,g_stamp.STAMP_PAGE,g_stamp.COR_FLAG,g_stamp.SEAL_TYPE,g_stamp.STORAGE_ADDRESS_ID,"
				+ " IMG_HEIGHT,FILE_PATH FROM g_stamp ");
		//如果corFlg为all 则查询corFlag == 4 8 9
		if(corFlag.equals("all")){
			ss.append("WHERE COR_FLAG = '4' OR COR_FLAG = '8' OR COR_FLAG = '9'");
		}else if(corFlag.equals("9")){
			ss.append("WHERE COR_FLAG = '9' OR COR_FLAG = '11'");
		}else{
			ss.append("WHERE COR_FLAG = '"+corFlag+"'");
		}
		if(StringUtil.isEmptyString(key)){
			ss.append("ORDER BY " + orderBy);
			sql = new Sql(ss.toString());
		}else{
			//查询报告章时，可以根据报告章的名字匹配筛选
			ss.append(" AND (COR_STAMP_NAME like '%"+key+"%' OR MATCH_NAME like '%"+key+"%' ) "
					+ " ORDER BY " + orderBy);
			sql = new Sql(ss.toString());
		}
        DataTable dt = SqlSessionManager.getDataTable(sql);
		if(dt !=null && dt.getRowCount() >0){
            JSONObject jsonObject = dt.toJSONObject();
            jsonObject.put("total",getCorStampListCount(corFlag,key));
            return jsonObject;
		}else{
			return new JSONObject();
		}
	}

	/**
	 * 获取总条数
	 * @param
	 * @param
	 * @param key
	 * @param
	 * @param
	 * @return
	 */
	public int getCorStampListCount(String corFlag,String key) {
		Sql sql = null;
		SqlDataSource dataSource = new SqlDataSource();
		StringBuffer ss = new StringBuffer("SELECT count(*) FROM g_stamp ");
		//如果corFlg为all 则查询corFlag == 4 8 9
		if(corFlag.equals("all")){
			ss.append("WHERE COR_FLAG = '4' OR COR_FLAG = '8' OR COR_FLAG = '9'");
		}else if(corFlag.equals("9")){
			ss.append("WHERE COR_FLAG = '9' OR COR_FLAG = '11'");
		}else{
			ss.append("WHERE COR_FLAG = '"+corFlag+"'");
		}
		if(StringUtil.isEmptyString(key)){
			sql = new Sql(ss.toString());
		}else{
			//查询报告章时，可以根据报告章的名字匹配筛选
			ss.append(" AND (COR_STAMP_NAME like '%"+key+"%' OR MATCH_NAME like '%"+key+"%' ) ");
			sql = new Sql(ss.toString());
		}
		DataTable dt = SqlSessionManager.getDataTable(sql);
		return  dt.getRow(0).getInt("COUNT(*)");
	}

	/**
	 * 查询第一个
	 * @return
	 */
	public GStamp queryDigitalStampCerNO(){
		GStamp gStamp = new GStamp();
		Sql sql = new Sql("SELECT CER_NO,ENCODE_SIGNTYPE,KEY_NAME from g_stamp WHERE COR_FLAG='8' LIMIT 0,1");
		DataTable dataTable = SqlSessionManager.getDataTable(sql);
		if(dataTable!=null&&dataTable.getRowCount()>0){
			DataRow dataRow = dataTable.getRow(0);
			gStamp.setCerNo(dataRow.getString("CER_NO"));
			gStamp.setKeyName(dataRow.getString("KEY_NAME"));
			gStamp.setEncodeSignType(dataRow.getInt("ENCODE_SIGNTYPE"));
		}
		return gStamp;
	}
	

	public List<GStamp> getMultiInterfaceSigns() {
		Sql sql = new Sql("SELECT ID,COR_STAMP_NAME AS NAME"
				 +" FROM g_stamp WHERE COR_FLAG='"+StampTypeEnum.ENTERPRISE_COR_STAMP_FLAG.getType()+"'"
				 );
		DataTable dt = SqlSessionManager.getDataTable(sql);
		List<GStamp> list = new ArrayList<GStamp>();
		if(dt !=null && dt.getRowCount()>0){
			for(int i = 0 ; i < dt.getRowCount() ; i ++){
				DataRow dr = dt.getRow(i);
				GStamp gs = new GStamp();
				parseDrToStamp(dr, gs);
				list.add(gs);
			}
		}
		
		return list;
	}
	/**
	 * 取得签章数据
	 * @param
	 * @return
	 * @throws Exception
	 */
	public  DataTable getDataTableList(String g_StampIds) throws Exception{
		Sql sql=new Sql("SELECT g_stamp.ID,g_stamp.FILE_PATH,g_stamp.MATCH_NAME,g_stamp.MATCH_NAME AS FILE_NAME,"
				+ " g_stamp.IMG_WIDTH,g_stamp.IMG_HEIGHT, "
				+ " g_stamp.POSITION,g_stamp.COR_FLAG,"
				+ " g_stamp.STAMP_X,g_stamp.STAMP_Y,g_stamp.STAMP_PAGE,g_stamp.COR_STAMP_NAME "
				+ " ,g_stamp.CER_NO,g_stamp.SEAL_TYPE,g_stamp.ENCODE_SIGNTYPE,g_stamp.STORAGE_ADDRESS_ID "
				+ " FROM g_stamp  "
				+ " WHERE ID in("+g_StampIds+")");
		//TODO 加载所有签章数据
		return SqlSessionManager.getDataTable(sql);
	}
	
	/**
	 * 获取公章对象
	 * @param stampID
	 * @return
	 * @throws Exception
	 */
	public  GStamp getCorStampObjectByStampId(String stampID) throws Exception{
		GStamp gs = new GStamp();
		DataTable dt = getCorStampByStampId(stampID);
		if(dt !=null && dt.getRowCount()>0){
			DataRow dr = dt.getRow(0);
			parseDrToStamp(dr,gs);
			gs.setId(stampID);
			return gs;
		}else{
			return gs;
		}
	}
	
	public void parseDrToStamp(DataRow dr,GStamp gs){
		JSONObject dd = dr.toJSONObject();
		gs.setId(dr.getString("ID"));
		gs.setImgWidth(dr.getString("IMG_WIDTH"));
		gs.setImgHeight(dr.getString("IMG_HEIGHT"));
		gs.setMatchName(dr.getString("MATCH_NAME"));
		gs.setFilePath(dr.getString("FILE_PATH"));
		gs.setStamp_x(dr.getString("STAMP_X"));
		gs.setStamp_y(dr.getString("STAMP_Y"));
		gs.setPosition(dr.getString("POSITION"));
		gs.setStampPage(dr.getString("STAMP_PAGE"));
		gs.setCorFlag(dr.getString("COR_FLAG"));
		gs.setCorStampName(dr.getString("COR_STAMP_NAME"));
		gs.setUserId(dr.getString("USER_ID"));
		gs.setCerNo(dr.getString("CER_NO"));
		gs.setKeyName(dr.getString("KEY_NAME"));
		gs.setSealType(dr.getString("SEAL_TYPE"));
		gs.setSealPageMode(dr.getString("SEAL_PAGE_MODE"));
		if(dd.has("STORAGE_ADDRESS_ID")) {
			gs.setStorageAddressId(dr.getString("STORAGE_ADDRESS_ID"));
		}
		if(dd.has("ENCODE_SIGNTYPE")){
			gs.setEncodeSignType(dr.getInt("ENCODE_SIGNTYPE"));
		}
		
		if(dd.has("NAME")){
			gs.setName(dr.getString("NAME"));
		}
	}
	
	
	

	/**
	 * 根据id获取章对象
	 * @param ID
	 * @return
	 * @throws Exception
	 */
	public GStamp getStampById(String ID)throws Exception{
		GStamp gs = new GStamp();
		DataTable dt = getDataTable(ID);
		if(dt !=null && dt.getRowCount()>0){
			DataRow dr = dt.getRow(0);
			parseDrToStamp(dr,gs);
			gs.setId(ID);
			return gs;
		}else{
			return null;
		}
	}
	
	/**
	 * 获取个人章对象
	 * @return
	 * @throws Exception
	 */
	public  GStamp getUserStampObjectByUserID(String userId) throws Exception{
		GStamp gs = new GStamp();
		DataTable dt = getUserStampDataTable(userId);
		if(dt !=null && dt.getRowCount()>0){
			DataRow dr = dt.getRow(0);
			parseDrToStamp(dr, gs);
			gs.setUserId(userId);
			return gs;
		}else{
			return null;
		}
	}
	
	/**
	 * 加载指定的签章
	 * @param g_StampIds
	 * @return
	 * @throws Exception
	 */
	public List<GStamp>  getStampsListByIds(String g_StampIds) throws Exception{
		DataTable dt =  getDataTableList(g_StampIds);
		List<GStamp> list = new ArrayList<GStamp>();
		if(dt !=null && dt.getRowCount()>0){
			for(int i = 0 ; i < dt.getRowCount() ; i ++){
				DataRow dr = dt.getRow(i);
				GStamp gs = new GStamp();
				parseDrToStamp(dr, gs);
				list.add(gs);
			}
		}
		
		return list;
	}
	
	/**
	 * 获取用户的COR_STAMP_NAME
	 * @param 
	 * @return
	 */
	public  String getUserCorStampName(String ID){
		String stampName = null;
		try{
			Sql sql = new Sql("SELECT COR_STAMP_NAME FROM g_stamp WHERE USER_ID = ?");
			sql.addParameter(ID);
			DataTable dt = SqlSessionManager.getDataTable(sql);
			DataRow row = dt.getRow(0);
			stampName = row.getString("COR_STAMP_NAME");
		}catch(Exception e){
			LoggerHelper.error(logger, e, "");
		}
		return stampName;
	}

	/*
	 * 根据条件获取第一条水印
	 */
	public GStamp getFirstWaterStampByCondition(String condition) {
		GStamp gs = new GStamp();
		DataTable dt = SqlSessionManager.getDataTable("select * from g_stamp where "+condition + " limit 1");
		if(dt !=null && dt.getRowCount()>0){
			DataRow dr = dt.getRow(0);
			parseDrToStamp(dr, gs);
			return gs;
		}else{
			return null;
		}
	}

	/*
	 * 获取水印列表--签名水印和草稿水印
	 */
	public JSONObject getWatermarkList(String orderBy, String key, Map<String, Object> dic,String WATERMARK_COR_STAMP_FLAG) {
		Sql sql = null;
		SqlDataSource dataSource = new SqlDataSource();
		if(StringUtil.isEmptyString(orderBy)){
			 orderBy = "g_stamp.UPDATE_TIME DESC";
		}
		StringBuffer ss = new StringBuffer("SELECT * FROM g_stamp ");
		if(!StringUtil.isEmptyString(WATERMARK_COR_STAMP_FLAG)){
			ss.append(" WHERE COR_FLAG = '"+WATERMARK_COR_STAMP_FLAG+"' ORDER BY " + orderBy);
			sql = new Sql(ss.toString());
		}else{
			if(StringUtil.isEmptyString(key)){
				ss.append(" WHERE COR_FLAG = '"+StampTypeEnum.WATERMARK_COR_STAMP_FLAG.getType()+"'"
						+ " OR COR_FLAG = '"+StampTypeEnum.DRAFT_WATERMARK_COR_STAMP_FLAG.getType()+"' ORDER BY " + orderBy);
				sql = new Sql(ss.toString());
			}else{
				//查询报告章时，可以根据报告章的名字匹配筛选
				ss.append(" WHERE (COR_STAMP_NAME like '%"+key+"%' ) "
						+ " AND (COR_FLAG = '"+StampTypeEnum.WATERMARK_COR_STAMP_FLAG.getType()+"'"
						+ " OR COR_FLAG = '"+StampTypeEnum.DRAFT_WATERMARK_COR_STAMP_FLAG.getType()+"')  ORDER BY " + orderBy);
				sql = new Sql(ss.toString());
			}
		}
		
		dataSource.getSelectCommands().add(sql);
		DataTable dt = dataSource.query(0, dic);
		if(dt !=null && dt.getRowCount() >0){
			return dt.toJSONObject();
		}else{
			return new JSONObject();
		}
	}
	

	/**
	 * 删除章
	 * @param gStampId
	 * @return
	 */
	public boolean delStampById(String gStampId){
		Sql sql = new Sql("DELETE FROM g_stamp WHERE ID = '"+gStampId+"'");
		return SqlSessionManager.execute(sql);
	}
	
	/**
	 * 根据查询条件获取章列表
	 * @param condition
	 * @return
	 */
	public List<GStamp> getGStampListByCondition(String condition){
		List<GStamp> list = new ArrayList<GStamp>();
		DataTable dt = SqlSessionManager.getDataTable("select * from g_stamp where "+condition);
		if(dt !=null && dt.getRowCount()>0){
			for(int i = 0 ; i < dt.getRowCount() ; i ++){
				DataRow dr = dt.getRow(i);
				GStamp gs = new GStamp();
				parseDrToStamp(dr, gs);
				list.add(gs);
			}
		}
		
		return list;
	}
	
	
	/**
	 * 获取资质章列表
	 * @param dic
	 * @return
	 */
	public DataTable getCredentialStampList(String key ,String orderBy,Map<String,Object> dic){
		SqlDataSource dataSource = new SqlDataSource();
		Sql sql = null;
			StringBuffer ss = new StringBuffer("SELECT g_stamp.ID,COR_STAMP_NAME AS CREDENTAIL_STAMP_NAME"
					+ " ,MATCH_NAME,IMG_WIDTH,"
					+ " STAMP_X,g_stamp.STAMP_Y,g_stamp.STAMP_PAGE,g_stamp.COR_FLAG,"
					+ " IMG_HEIGHT,FILE_PATH FROM g_stamp ");
			if(StringUtil.isEmptyString(key)){
				ss.append(" WHERE COR_FLAG = '2' OR COR_FLAG = '6' ORDER BY " + orderBy);
				sql = new Sql(ss.toString());
			}else{
				ss.append(" WHERE (COR_FLAG = '2'  OR COR_FLAG = '6'"
						+ " )AND MATCH_NAME like '%"+key+"%' OR COR_STAMP_NAME LIKE '%"+key+"%'   ORDER BY " + orderBy);
				sql = new Sql(ss.toString());
			}

			dataSource.getSelectCommands().add(sql);

			DataTable dt = dataSource.query(0, dic);
			return dt;
	}
	
	
	
	/**
	 * 签章授权列表
	 */
	public  DataTable getUserDigitalStampList(String key,int limit,int start) throws Exception{
		//获取查询关键字
		SqlDataSource dataSource = new SqlDataSource();
		StringBuffer s = new StringBuffer("select u_user.`Name`,g_user_cor_stamp.USER_ID from  g_user_cor_stamp "+
										 " LEFT OUTER JOIN u_user "+
										 " ON g_user_cor_stamp.USER_ID =u_user.ID WHERE u_user.`Name` like '%"+key+"%' "+
										 " GROUP BY g_user_cor_stamp.USER_ID,u_user.`Name`"+
										 " ORDER BY g_user_cor_stamp.CREATE_TIME DESC limit "+start+","+limit);
		Sql sql  = new Sql(s.toString());
		DataTable dt = SqlSessionManager.getDataTable(sql);
		return dt;
		
	}

	public  long getUserDigitalStampCount(String key,int limit,int start) throws Exception{
		//获取查询关键字
		SqlDataSource dataSource = new SqlDataSource();
		StringBuffer s = new StringBuffer("select count(g_user_cor_stamp.ID) from  g_user_cor_stamp "+
				" LEFT OUTER JOIN u_user "+
				" ON g_user_cor_stamp.USER_ID =u_user.ID WHERE u_user.`Name` like '%"+key+"%' "+
				" GROUP BY g_user_cor_stamp.USER_ID,g_user_cor_stamp.CREATE_TIME"+
				" ORDER BY g_user_cor_stamp.CREATE_TIME DESC ");
		Sql sql  = new Sql(s.toString());
		long count = SqlSessionManager.getCount(sql);
		return count;

	}
	
	
	/**
	 * 根据用户id获取用章缩图
	 */
	public  DataTable getUserDigitalStampPicList(String userId,int type) throws Exception{
		StringBuffer s = new StringBuffer("select g_stamp.FILE_PATH, g_stamp.STORAGE_ADDRESS_ID  from g_user_cor_stamp LEFT OUTER JOIN g_stamp "+
										" ON g_user_cor_stamp.USER_AUTHORIZATION_ID = g_stamp.ID"+
										" where g_user_cor_stamp.USER_ID='"+userId+"'and g_user_cor_stamp.AUTHORIZATION_TYPE="+type);
		Sql sql  = new Sql(s.toString());
		return SqlSessionManager.getDataTable(sql);
	}
	
	public String deleteDir(String id){
		Result result = new Result(true);
		IStore store = StoreManager.create("dirmanagers");
		if(store.removeById(id)>0){
		}else{
			result.setResult(false);
		}
		return result.toString();
	}

	public MsgMail getBase(){
		MsgMail msgMail = new MsgMail();
		IStore store = StoreManager.create("msg_mail");
		IObject object = store.get("1");
 		if(object!=null){
 			msgMail.setMail(object.getString("MAIL"));
 			msgMail.setSmtp(object.getString("SMTP"));
 			msgMail.setName(object.getString("NAME"));
 			msgMail.setContent(object.getString("CONTENT"));
 		}
 		return msgMail;
	}
	
}
